﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Drawing;

namespace 人事工资管理系统.PMSClass
{
    class DBOperate
    { 
        SqlConnection conn = PMSClass.DBConnection.MyConnection();
       public int OperateData(string strSql)
        {
           
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            int i = (int)cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public void BindDataGridView(DataGridView dgv,string sql)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);//创建数据适配器对象
            DataSet ds = new DataSet();//创建数据集对象
            sda.Fill(ds);//填充数据集
            dgv.DataSource = ds.Tables[0];//绑定到数据表
            ds.Dispose();//释放资源

        }
        public int HumanNum(string strsql)
        {
            conn.Open();//打开数据库连接
            SqlCommand cmd = new SqlCommand(strsql, conn);//创建命令对象
            int i = (int)cmd.ExecuteScalar();//执行SQL命令
            conn.Close();//关闭数据库连接
            return i;//返回数值

        }
        public DataSet GetTable(string sql)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);//创建数据适配器对象
            DataSet ds = new DataSet();//创建数据集
            sda.Fill(ds);//填充数据集
            ds.Dispose();//释放资源
            return ds;//返回数据集
        }
        public void BindDropdownlist(string strTable, ComboBox cb, int i)
        {
            conn.Open();//打开数据库连接
            SqlCommand cmd = new SqlCommand(//创建命令对象
                "select * from " + strTable, conn);
            SqlDataReader sdr = cmd.ExecuteReader();//得到数据读取器
            while (sdr.Read())
            {
                cb.Items.Add(sdr[i].ToString());//添加信息
            }
            conn.Close();//关闭数据库连接
        }  
          public void Read_Image(OpenFileDialog openF,PictureBox MyImage)
        {
            openF.Filter = "*.jpg|*.jpg|*.bmp|*.bmp";
            if(openF.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    MyImage.Image = System.Drawing.Image.
                        FromFile(openF.FileName);
                }
                catch
                {
                    MessageBox.Show("您选择的图片不能被读取或文件类型不对!",
                    "错误",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                }
            }

        }



  public void SaveImage(string MID, OpenFileDialog openF)
        {
            string P_str = openF.FileName;
            FileStream fs = new FileStream(
                P_str, FileMode.Open, FileAccess.Read);
            BinaryReader br = new BinaryReader(fs);
            byte[] imgBytesIn = br.ReadBytes((int)fs.Length);
            conn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(
                "update tb_employee Set employeePhoto=@Photo where employeeID=" + MID);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
            cmd.Parameters.Add("@Photo",SqlDbType.Binary).Value = imgBytesIn;
            cmd.ExecuteNonQuery();
            conn.Close();

        }

  public void Get_Image(String ygname, PictureBox pb)
        {
            byte[] imagebytes = null;
            conn.Open();
            SqlCommand com = new SqlCommand(
                "select*from tb_employee where employeeID='" + ygname + "'", conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                imagebytes = (byte[])dr.GetValue(11);
            }
            dr.Close();
            conn.Close();
            MemoryStream ms = new MemoryStream(imagebytes);
            Bitmap bmpt = new Bitmap(ms);
            pb.Image = bmpt;
        }
  public decimal GYSD(int pay)
  {
      decimal tax = 0;
      int Y = pay - 1600;
      if (pay <= 1600)
      {
          tax = 0;
      }
      else
      {
          if (Y >= 0 | Y <= 500)
          {
              tax = (decimal)(Y * 0.05);
          }
          else
          {
              if (Y > 500 | Y <= 2000)
              {
                  tax = (decimal)(Y * 0.1 - 25);
              }
              else
              {
                  if (Y > 2000 | Y <= 5000)
                  {
                      tax = (decimal)(Y * 0.15 - 125);
                  }
                  else
                  {
                      if (Y > 5000 | Y <= 20000)
                      {
                          tax = (decimal)(Y * 0.2 - 375);
                      }
                      else
                      {
                          if (Y > 20000 | Y <= 40000)
                          {
                              tax = (decimal)(Y * 0.25 - 1375);
                          }
                          else
                          {
                              if (Y > 4000 | Y <= 60000)
                              {
                                  tax = (decimal)(Y * 0.3 - 3375);
                              }
                              else
                              {
                                  if (Y > 60000 | Y <= 80000)
                                  {
                                      tax = (decimal)(Y * 0.35 - 6375);
                                  }
                                  else
                                  {
                                      if (Y > 80000 | Y <= 100000)
                                      {
                                          tax = (decimal)(Y * 0.4 - 10375);
                                      }
                                      else
                                      {
                                          if (Y > 100000)
                                          {
                                              tax = (decimal)(Y * 0.45 - 15375);
                                          }
                                      }
                                  }
                              }
                          }
                      }
                  }
              }
          }
      }
      return tax;
  }

    }
}
